using System;
using System.Collections.Generic;
using System.Text;

using System.Data.SqlClient;
using System.Data;

namespace Serialcoder.Erp.Tools.PatchCreator.Runtime
{
    public static class DAL
    {
        public static Patch CreateNew()
        {
            Patch patch = new Patch();
            patch.CreationDate = DateTime.Now;
            patch.Author = Properties.Settings.Default.Auteur;
            return patch;
        }
        public static List<Patch> GetAll()
        {
            List<Patch> list = new List<Patch>();

            using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = "select * from SchemaPatch order by id desc";

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Patch patch = new Patch();
                    patch.Id = (int)reader["id"];
                    patch.Author = (string)reader["createdBy"];
                    patch.Description = Convert.IsDBNull(reader["description"]) ? string.Empty : (string)reader["Description"];
                    patch.CreationDate = (DateTime)reader["CreationDate"];
                    patch.Sql = Convert.IsDBNull( reader["Sql"]) ? string.Empty : (string)reader["sql"];

                    list.Add(patch);
                }

                reader.Close();
                connection.Close();
            }

            return list;
        }

        public static void Save(Patch patch)
        {
            using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.ConnectionString))
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = @"INSERT INTO SchemaPatch (createdBy, description, creationdate, sql) values (@createdBy, @description, @creationdate, @sql)
                SELECT SCOPE_IDENTITY()";
                command.Parameters.Add(new SqlParameter("CreatedBy", patch.Author));
                command.Parameters.Add(new SqlParameter("description", patch.Description));
                command.Parameters.Add(new SqlParameter("creationdate", patch.CreationDate));
                command.Parameters.Add(new SqlParameter("sql", patch.Sql));

                object id = command.ExecuteScalar();

                patch.Id = Convert.ToInt32(id);

                connection.Close();
            }
        }
    }
}
